package cn.com.zsc.income;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Hashtable;
import java.util.Vector;

import javax.servlet.ServletException;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;

import cn.com.zsc.util.ConstValue;

import com.jarp.db.DAO;

/**
 * 收入管理类
 * @author 杨景
 *
 */
public class IncomeManage {
	/**
	 * 获取未销时的收入信息
	 * 
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public String getIncome() {
		String sql = "select " +
							"B.CL_NAME AS SHOW_INCOMETYPE, " +
							"D.CL_NAME AS SHOW_DISCOUNT, " +
							"A.* " +
					 "from ic_manage A " +
					 		"left join cm_commlist B on A.INCOMETYPE=B.CL_CODE " +
					 		"left join cm_commlist D on A.DISCOUNT=D.CL_CODE " +
			 		 "where ENDTIME is null order by A.ID desc";

		DAO dao = new DAO();
		
		String resultStr = ConstValue.EMPTY_RECORD_NOT_PAGEING;
		
		try {
			Vector<Hashtable<String, String>> v = dao.getDataSet(ConstValue.DBNAME, sql);

			if (v != null && v.size() > 0) {
				for (int i=0; i<v.size(); i++) {
					v.get(i).put("SHOW_ATTRACTIONS", getAttractionsByIds(v.get(i).get("ATTRACTIONS")));
				}
				
				JSONArray json = JSONArray.fromObject(v);
				
				resultStr = json.toString();				
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return resultStr;
	}

	/**
	 * 新增收入
	 * @param jso
	 * @return
	 */
	public String insertIncome(String jso) {
		JSONObject jsobject = JSONObject.fromObject(jso);
		
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	
		String guideID = jsobject.getString("guideID");
		String guideName = jsobject.getString("guideName");
		String incomeType = jsobject.getString("incomeType");
		String attractions = jsobject.getString("attractions");
		String reception = jsobject.getString("reception");
		String source = jsobject.getString("source");
		String term = jsobject.getString("term");
		String noticeunit = jsobject.getString("noticeunit");
		String notifier = jsobject.getString("notifier");
		String num = jsobject.getString("num");
		String statwin = jsobject.getString("statwin");
		String startTime = df.format(new Date());
		String endTime = null;
		Integer invoice = "".equals(jsobject.getString("invoice"))?null:Integer.parseInt(jsobject.getString("invoice"));
		Double eamt = "".equals(jsobject.getString("eamt"))?null:Double.parseDouble(jsobject.getString("eamt"));
		Double discount = "".equals(jsobject.getString("discount"))?null:Double.parseDouble(jsobject.getString("discount"));
		String ratio = jsobject.getString("ratio");
		Double samt = "".equals(jsobject.getString("samt"))?null:Double.parseDouble(jsobject.getString("samt"));
		Double aamt = "".equals(jsobject.getString("aamt"))?null:Double.parseDouble(jsobject.getString("aamt"));
		String settleType = jsobject.getString("settleType");
		Double deduct = "".equals(jsobject.getString("deduct"))?null:Double.parseDouble(jsobject.getString("deduct"));
		Integer feedback = "".equals(jsobject.getString("feedback"))?null:Integer.parseInt(jsobject.getString("feedback"));
		String remarked = jsobject.getString("remarked");
		String remark = jsobject.getString("remark");
		Integer points = null;
		
		if ("".equals(jsobject.getString("points"))) {
			points = attractions.split(",").length;
		} else {
			points = Integer.parseInt(jsobject.getString("points"));
		}
		
		if (!isGuideRelease(guideID)) {
			return "导游仍在带团中，请先销时";
		}
		
		String sql = "insert into ic_manage values(default,'" + guideID + "','"
				+ guideName + "'," + incomeType + ",'" + attractions + "','"
				+ reception + "','" + source + "','" + term + "','"
				+ noticeunit + "','" + notifier + "'," + num + ",'" + statwin
				+ "','" + startTime + "'," + endTime + "," + invoice + ","
				+ eamt + "," + discount + ",'" + ratio + "'," + samt + ","
				+ aamt + ",'" + settleType + "'," + deduct + "," + feedback
				+ ",'" + remarked + "','" + remark + "'," + points + ")";
		
		DAO dao = new DAO();
		
		int resCount = 0;
		
		try {
			resCount = dao.execCommand(ConstValue.DBNAME, sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		if (resCount > 0) {
			return "success";
		} else {
			return "数据库操作失败";
		}
	}
	
	/**
	 * 更新收入
	 * @param jso
	 * @return
	 */
	public String updateIncome(String jso) {	
		JSONObject jsobject = JSONObject.fromObject(jso);
		
		// 更新ic_manage所需字段
		String id = jsobject.getString("id");
		String endTime = jsobject.getString("endTime");
		Integer feedback = "".equals(jsobject.getString("feedback"))?null:Integer.parseInt(jsobject.getString("feedback"));
		Double deduct = "".equals(jsobject.getString("deduct"))?null:Double.parseDouble(jsobject.getString("deduct"));
		String remarked = jsobject.getString("remarked");
		
		// 更新ic_day_business所需字段
		String today = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
		boolean isExistIncomeToday = this.isExistIncomeToday(today);
		Integer starLevel = "".equals(jsobject.getString("starLevel"))?null:Integer.parseInt(jsobject.getString("starLevel"));
		Integer attractionsNums = "".equals(jsobject.getString("attractionsNums"))?null:Integer.parseInt(jsobject.getString("attractionsNums"));
		Integer incomeType = "".equals(jsobject.getString("incomeType"))?null:Integer.parseInt(jsobject.getString("incomeType"));
		Double samt = "".equals(jsobject.getString("samt"))?null:Double.parseDouble(jsobject.getString("samt"));
		String guideID = jsobject.getString("guideID");
		String guideName = jsobject.getString("guideName");
	
		String updateIcDayBusiness = "";
		
		if(!isExistIncomeToday) {
			if (incomeType != 208) {
				if (starLevel == 26) {
					if (attractionsNums == 1) {
						updateIcDayBusiness = "insert ic_day_business values(default,'" + today + "','" + guideID 
						+ "','" + guideName + "',"+ starLevel + ",'"+ samt+ "',null,null,null,null,null," + attractionsNums + "," + deduct + ",null)";
					} else if(attractionsNums == 2) {
						updateIcDayBusiness = "insert ic_day_business values(default,'" + today + "','" + guideID 
						+ "','" + guideName + "',"+ starLevel + ",null,'"+ samt +"',null,null,null,null," + attractionsNums + "," + deduct + ",null)";
					} else if(attractionsNums >= 3) {
						updateIcDayBusiness = "insert ic_day_business values(default,'" + today + "','" + guideID 
						+ "','" + guideName + "',"+ starLevel + ",null,null,'"+ samt +"',null,null,null," + attractionsNums + "," + deduct + ",null)";
					}
				} else if(starLevel == 27) {
					if (attractionsNums == 1) {
						updateIcDayBusiness = "insert ic_day_business values(default,'" + today + "','" + guideID 
						+ "','" + guideName + "',"+ starLevel + ",null,'"+ samt +"',null,null,null,null," + attractionsNums + "," + deduct + ",null)";
					} else if(attractionsNums == 2) {
						updateIcDayBusiness = "insert ic_day_business values(default,'" + today + "','" + guideID 
						+ "','" + guideName + "',"+ starLevel + ",null,null,'"+ samt +"',null,null,null," + attractionsNums + "," + deduct + ",null)";
					} else if(attractionsNums >= 3) {
						updateIcDayBusiness = "insert ic_day_business values(default,'" + today + "','" + guideID 
						+ "','" + guideName + "',"+ starLevel + ",null,null,null,'"+ samt +"',null,null," + attractionsNums + "," + deduct + ",null)";
					}
				} else if(starLevel == 28) {
					if (attractionsNums == 1) {
						updateIcDayBusiness = "insert ic_day_business values(default,'" + today + "','" + guideID 
						+ "','" + guideName + "',"+ starLevel + ",null,null,'"+ samt +"',null,null,null," + attractionsNums + "," + deduct + ",null)";
					} else if(attractionsNums == 2) {
						updateIcDayBusiness = "insert ic_day_business values(default,'" + today + "','" + guideID 
						+ "','" + guideName + "',"+ starLevel + ",null,null,null,'"+ samt +"',null,null," + attractionsNums + "," + deduct + ",null)";
					} else if(attractionsNums >= 3) {
						updateIcDayBusiness = "insert ic_day_business values(default,'" + today + "','" + guideID 
						+ "','" + guideName + "',"+ starLevel + ",null,null,null,null,'"+ samt +"',null," + attractionsNums + "," + deduct + ",null)";
					}
				} else {
					updateIcDayBusiness = "insert ic_day_business values(default,'" + today + "','" + guideID 
					+ "','" + guideName + "',"+ starLevel + ",'"+ samt+ "',null,null,null,null,null," + attractionsNums + "," + deduct + ",null)";
				}
			} else {
				updateIcDayBusiness = "insert ic_day_business values(default,'" + today + "','" + guideID 
				+ "','" + guideName + "',"+ starLevel + ",null,null,null,null,null,'"+ deduct +"'," + attractionsNums + "," + deduct + ",null)";
			}	
		} else {
			if (incomeType != 208) {
				if (starLevel == 26) {
					if (attractionsNums == 1) {
						updateIcDayBusiness = "update ic_day_business set B35=B35+"+ samt +",POINT=POINT+"+ attractionsNums +",AMOUNT=AMOUNT+" + deduct;
					} else if(attractionsNums == 2) {
						updateIcDayBusiness = "update ic_day_business set B40=B40+"+ samt +",POINT=POINT+"+ attractionsNums +",AMOUNT=AMOUNT+" + deduct;
					} else if(attractionsNums >= 3) {
						updateIcDayBusiness = "update ic_day_business set B45=B45+"+ samt +",POINT=POINT+"+ attractionsNums +",AMOUNT=AMOUNT+" + deduct;
					}
				} else if(starLevel == 27) {
					if (attractionsNums == 1) {
						updateIcDayBusiness = "update ic_day_business set B40=B40+"+ samt +",POINT=POINT+"+ attractionsNums +",AMOUNT=AMOUNT+" + deduct;
					} else if(attractionsNums == 2) {
						updateIcDayBusiness = "update ic_day_business set B45=B45+"+ samt +",POINT=POINT+"+ attractionsNums +",AMOUNT=AMOUNT+" + deduct;
					} else if(attractionsNums >= 3) {
						updateIcDayBusiness = "update ic_day_business set B50=B50+"+ samt +",POINT=POINT+"+ attractionsNums +",AMOUNT=AMOUNT+" + deduct;
					}
				} else if(starLevel == 28) {
					if (attractionsNums == 1) {
						updateIcDayBusiness = "update ic_day_business set B45=B45+"+ samt +",POINT=POINT+"+ attractionsNums +",AMOUNT=AMOUNT+" + deduct;
					} else if(attractionsNums == 2) {
						updateIcDayBusiness = "update ic_day_business set B50=B50+"+ samt +",POINT=POINT+"+ attractionsNums +",AMOUNT=AMOUNT+" + deduct;
					} else if(attractionsNums >= 3) {
						updateIcDayBusiness = "update ic_day_business set B55=B55+"+ samt +",POINT=POINT+"+ attractionsNums +",AMOUNT=AMOUNT+" + deduct;
					}
				} else {
					updateIcDayBusiness = "update ic_day_business set B35=B35+"+ samt +",POINT=POINT+"+ attractionsNums +",AMOUNT=AMOUNT+" + deduct;
				}
			} else {
				updateIcDayBusiness = "insert ic_day_business values(default,'" + today + "','" + guideID 
				+ "','" + guideName + "',"+ starLevel + ",null,null,null,null,null,'"+ deduct +"'," + attractionsNums + "," + deduct + ",null)";
			}	
		}
		
		String updateIcManage = "update ic_manage set ENDTIME='" + endTime + "',FEEDBACK="
				+ feedback + ",DEDUCT=" + deduct + ",REMARKED='" + remarked
				+ "'  where ID=" + id;
		
		ArrayList<String> sqlList = new ArrayList<String>();
		
		sqlList.add(updateIcDayBusiness);
		sqlList.add(updateIcManage);
		
		DAO dao = new DAO();
		
		boolean flag = false;
		
		try {
			flag = dao.execCommand(ConstValue.DBNAME, sqlList);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		if (flag) {
			return "success";
		} else {
			return "数据库操作失败";
		}
	}
	
	/**
	 * 根据观光点ID查询观光点
	 * @param attractionsIds
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public String getAttractionsByIds(String attractionsIds) {
		if(attractionsIds.startsWith("[")) {
			return attractionsIds.substring(1, attractionsIds.length() - 1);
		}
		
		String sql = "select CL_NAME from cm_commlist where CL_CODE in (" + attractionsIds + ")";
		
		DAO dao = new DAO();
		
		String attractions = "";
		
		try {
			Vector<Hashtable<String, String>> v = dao.getDataSet(ConstValue.DBNAME, sql);
			
			if (v != null && v.size() > 0) {
				for (int i=0; i<v.size(); i++) {
					attractions += v.get(i).get("CL_NAME") + ",";
				}
				
				attractions = attractions.substring(0, attractions.length()-1);
			}
		} catch (ServletException e) {
			e.printStackTrace();
		}
		
		return attractions;
	}
	
	/**
	 * 计算观光点总费用
	 * @param jso
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public String calculateFee(String jso) {
		JSONObject jsobject = JSONObject.fromObject(jso);
		
		String attractionsIds = jsobject.getString("attractionsIds");
		
		String[] attIds = null;
		
		int totaoFee = 0;
		
		if(!StringUtils.isEmpty(attractionsIds)) {
			attIds = attractionsIds.split(ConstValue.ATTRACTIONS_SEPARATOR);
		}
		
		if(attIds!=null && attIds.length!=0) {
			String sql = null;
			Vector<Hashtable<String, String>> v = null;
			
			DAO dao = new DAO();
			
			try {
				for (int i=0; i<attIds.length; i++) {
					sql = "select PARM from cm_commlist where CL_CODE = " + attIds[i];
					
					v = dao.getDataSet(ConstValue.DBNAME, sql);
					
					totaoFee += Integer.parseInt(v.get(0).get("PARM"));
				}	
			} catch (ServletException e) {
				e.printStackTrace();
			}
		}
		
		if (totaoFee != 0) {
			return String.valueOf(totaoFee);
		} else {
			return "数据库操作失败";
		}
	}
	
	/**
	 * 获取员工星级
	 * @param jso
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public String getEmpStarLevel(String jso) {
		JSONObject jsobject = JSONObject.fromObject(jso);
		
		String guideID = jsobject.getString("guideID");
		
		String sql = "select STARLEVEL from hr_employee where WORKID = '" + guideID + "'" ;
		
		DAO dao = new DAO();
		
		String starLevel = "";
			
		try {
			Vector<Hashtable<String, String>> v = dao.getDataSet(ConstValue.DBNAME, sql);
			
			if(v!=null && v.size()>0) {
				starLevel = v.get(0).get("STARLEVEL");
			}
		} catch (ServletException e) {
			e.printStackTrace();
			return "数据库操作失败";
		}
		
		return starLevel;
	}
	
	/**
	 * 查询当天是否有带团记录
	 * @param today
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public boolean isExistIncomeToday(String today) {
		String sql = "select ID from ic_manage where left(ENDTIME, 10)='" + today + "'";
		
		DAO dao = new DAO();
		
		boolean flag = false;
	
		try {
			Vector<Hashtable<String, String>> v = dao.getDataSet(ConstValue.DBNAME, sql);
			
			if (v != null && v.size() > 0) {
				flag = true;
			}
		} catch (ServletException e) {
			e.printStackTrace();
		}
		
		return flag;
	}
	
	/**
	 * 判断导游是否在带团中
	 * @param guideID
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public boolean isGuideRelease(String guideID) {
		String sql = "select ID from ic_manage where GUIDEID='" + guideID + "' and ENDTIME is null";
		
		DAO dao = new DAO();
		
		boolean flag = true;
	
		try {
			Vector<Hashtable<String, String>> v = dao.getDataSet(ConstValue.DBNAME, sql);
			
			if (v != null && v.size() > 0) {
				flag = false;
			}
		} catch (ServletException e) {
			e.printStackTrace();
		}
		
		return flag;
	}
	
	/**
	 * 获取员工排序信息
	 * 
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public String getEmpSortInfo() {
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		String today = df.format(new Date());
		
		String getEmpSql = "select B.CL_NAME AS SHOW_TERM, A.* from ic_guidesort A left join cm_commlist B on A.TERM=B.CL_CODE where A.DATE='" + today + "' order by A.NO,A.IDX";

		DAO dao = new DAO();
		
		String resultStr = ConstValue.EMPTY_RECORD_NOT_PAGEING;
		
		try {
			Vector<Hashtable<String, String>> v = dao.getDataSet(ConstValue.DBNAME, getEmpSql);
			
			if (v != null && v.size() > 0) {
				JSONArray json = JSONArray.fromObject(v);
				
				resultStr = json.toString();
			} else {
				String insertEmpSql = "insert into ic_guidesort (DATE,GUIDEID,GUIDENAME,TERM) select A.RECDATE,A.WORKID,"
						+ "B.ENAME,B.TERM from hr_atdrecord A left join hr_employee B on A.WORKID=B.WORKID where A.RECDATE='"
						+ today + "' GROUP BY B.TERM, B.ENAME";
				
				int num = dao.execCommand(ConstValue.DBNAME, insertEmpSql);
				
				if (num > 0) {
					resultStr = getEmpSortInfo();
				}
			}				
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return resultStr;
	}
	
	/**
	 * 员工排序
	 * @return
	 */
	public String sortEmployee(String jso) {
		JSONArray array = JSONArray.fromObject(jso);
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		String today = df.format(new Date());

		ArrayList<String> sqlList = new ArrayList<String>();
		DAO dao = new DAO();
		
		String delSql = "delete from ic_guidesort where date='" + today + "'";
		sqlList.add(delSql);
				
		for (int i=0; i<array.length(); i++) {
			JSONObject jsobject = array.getJSONObject(i);
			
			String guideID = jsobject.getString("guideID");
			String guideName = jsobject.getString("guideName");
			Integer idx = "".equals(jsobject.getString("idx"))?null:Integer.parseInt(jsobject.getString("idx"));
			Integer term = "".equals(jsobject.getString("term"))?null:Integer.parseInt(jsobject.getString("term"));
			Integer no = "".equals(jsobject.getString("no"))?0:Integer.parseInt(jsobject.getString("no"));
			String remarked = null;
		
			sqlList.add("insert into ic_guidesort values(default, '" + today
					+ "','" + guideID + "','" + guideName + "'," + term + ","
					+ idx + "," + no + "," + remarked + ")");
		}
		
		try {
			boolean flag = dao.execCommand(ConstValue.DBNAME, sqlList);
			
			if (flag) {
				return "success";
			} else {
				return "数据库操作失败";
			}
		} catch (Exception e) {
			e.printStackTrace();
			return "数据库操作失败";
		}
	}
	
	/**
	 * 更新带团次数
	 * @param jso
	 * @return
	 */
	public String updateGuideNum(String jso ) {
		JSONObject jsobject = JSONObject.fromObject(jso);
		
		String guideID = jsobject.getString("guideID");
		
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		String today = df.format(new Date());
		
		String sql = "update ic_guidesort set NO=NO+1 where DATE='" + today + "' AND GUIDEID='" + guideID + "'";

		DAO dao = new DAO();
	
		try {
			int num = dao.execCommand(ConstValue.DBNAME, sql);
			
			if (num > 0) {
				return "success";
			} else {
				return "数据库操作失败";
			}
		} catch (Exception e) {
			e.printStackTrace();
			return "数据库操作失败";
		}
	}
}
